﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using stwh_Common.DBUtility;//Please add references

namespace stwh_DAL
{
	/// <summary>
	/// 数据访问类:stwh_staff
	/// </summary>
    public partial class stwh_staffDAL : BaseDAL
	{
		public stwh_staffDAL()
		{}
		#region  BasicMethod
        /// <summary>
        /// 分页获取文本消息
        /// </summary>
        /// <param name="FieldColumn">排序的列名</param>
        /// <param name="FieldOrder">降序排列还是升序排列</param>
        /// <param name="If">查询条件</param>
        /// <param name="pageSize">每页显示的条数</param>
        /// <param name="pageNumber">页码</param>
        /// <param name="selectCount">查询的总记录条数</param>
        /// <param name="d_peopleCount">总记录条数（不带条件）</param>
        /// <returns></returns>
        public DataSet GetListByPage(string FieldColumn, string FieldOrder, string If, int pageSize, int pageNumber, ref int selectCount, ref int d_peopleCount)
        {
            object obj = DbHelperSQL.GetSingle("select count(1) from view_staff");
            d_peopleCount = obj != null ? int.Parse(obj.ToString()) : 0;
            return DbHelperSQL.PageData("view_staff", "stwh_sid", FieldColumn, FieldOrder, "stwh_sid,stwh_dtid,stwh_snumber,stwh_sname,stwh_ssex,stwh_srztime,stwh_szw,stwh_stel,stwh_sbirthday,stwh_sxueli,stwh_ssfz,stwh_syqmoney,stwh_szzmoney,stwh_siszz,stwh_szztime,stwh_sisht,stwh_shttime,stwh_sislz,stwh_slztime,stwh_sremark,stwh_saddtime,stwh_dtname", If, pageSize, pageNumber, ref selectCount);
        }

        /// <summary>
        /// 分页获取文本消息
        /// </summary>
        /// <param name="FieldColumn">排序的列名</param>
        /// <param name="FieldOrder">降序排列还是升序排列</param>
        /// <param name="If">查询条件</param>
        /// <param name="pageSize">每页显示的条数</param>
        /// <param name="pageNumber">页码</param>
        /// <param name="selectCount">查询的总记录条数</param>
        /// <param name="d_peopleCount">总记录条数（带条件）</param>
        /// <returns></returns>
        public DataSet GetListByPage(string FieldColumn, string FieldOrder, string If, int pageSize, int pageNumber, ref int selectCount, ref int d_peopleCount, int flag)
        {
            object obj = DbHelperSQL.GetSingle("select count(1) from view_staff where " + If);
            d_peopleCount = obj != null ? int.Parse(obj.ToString()) : 0;
            return DbHelperSQL.PageData("view_staff", "stwh_sid", FieldColumn, FieldOrder, "stwh_sid,stwh_dtid,stwh_snumber,stwh_sname,stwh_ssex,stwh_srztime,stwh_szw,stwh_stel,stwh_sbirthday,stwh_sxueli,stwh_ssfz,stwh_syqmoney,stwh_szzmoney,stwh_siszz,stwh_szztime,stwh_sisht,stwh_shttime,stwh_sislz,stwh_slztime,stwh_sremark,stwh_saddtime,stwh_dtname", If, pageSize, pageNumber, ref selectCount);
        }

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperSQL.GetMaxID("stwh_sid", "stwh_staff"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int stwh_sid)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from stwh_staff");
			strSql.Append(" where stwh_sid=@stwh_sid");
			SqlParameter[] parameters = {
					new SqlParameter("@stwh_sid", SqlDbType.Int,4)
			};
			parameters[0].Value = stwh_sid;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public int Add(stwh_Model.stwh_staff model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into stwh_staff(");
			strSql.Append("stwh_dtid,stwh_snumber,stwh_sname,stwh_ssex,stwh_srztime,stwh_szw,stwh_stel,stwh_sbirthday,stwh_sxueli,stwh_ssfz,stwh_syqmoney,stwh_szzmoney,stwh_siszz,stwh_szztime,stwh_sisht,stwh_shttime,stwh_sislz,stwh_slztime,stwh_sremark,stwh_saddtime)");
			strSql.Append(" values (");
			strSql.Append("@stwh_dtid,@stwh_snumber,@stwh_sname,@stwh_ssex,@stwh_srztime,@stwh_szw,@stwh_stel,@stwh_sbirthday,@stwh_sxueli,@stwh_ssfz,@stwh_syqmoney,@stwh_szzmoney,@stwh_siszz,@stwh_szztime,@stwh_sisht,@stwh_shttime,@stwh_sislz,@stwh_slztime,@stwh_sremark,@stwh_saddtime)");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@stwh_dtid", SqlDbType.Int,4),
					new SqlParameter("@stwh_snumber", SqlDbType.Int,4),
					new SqlParameter("@stwh_sname", SqlDbType.NVarChar,200),
					new SqlParameter("@stwh_ssex", SqlDbType.Int,4),
					new SqlParameter("@stwh_srztime", SqlDbType.DateTime),
					new SqlParameter("@stwh_szw", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_stel", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_sbirthday", SqlDbType.DateTime),
					new SqlParameter("@stwh_sxueli", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_ssfz", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_syqmoney", SqlDbType.Int,4),
					new SqlParameter("@stwh_szzmoney", SqlDbType.Int,4),
					new SqlParameter("@stwh_siszz", SqlDbType.Int,4),
					new SqlParameter("@stwh_szztime", SqlDbType.DateTime),
					new SqlParameter("@stwh_sisht", SqlDbType.Int,4),
					new SqlParameter("@stwh_shttime", SqlDbType.DateTime),
					new SqlParameter("@stwh_sislz", SqlDbType.Int,4),
					new SqlParameter("@stwh_slztime", SqlDbType.DateTime),
					new SqlParameter("@stwh_sremark", SqlDbType.NVarChar,500),
					new SqlParameter("@stwh_saddtime", SqlDbType.DateTime)};
			parameters[0].Value = model.stwh_dtid;
			parameters[1].Value = model.stwh_snumber;
			parameters[2].Value = model.stwh_sname;
			parameters[3].Value = model.stwh_ssex;
			parameters[4].Value = model.stwh_srztime;
			parameters[5].Value = model.stwh_szw;
			parameters[6].Value = model.stwh_stel;
			parameters[7].Value = model.stwh_sbirthday;
			parameters[8].Value = model.stwh_sxueli;
			parameters[9].Value = model.stwh_ssfz;
			parameters[10].Value = model.stwh_syqmoney;
			parameters[11].Value = model.stwh_szzmoney;
			parameters[12].Value = model.stwh_siszz;
			parameters[13].Value = model.stwh_szztime;
			parameters[14].Value = model.stwh_sisht;
			parameters[15].Value = model.stwh_shttime;
			parameters[16].Value = model.stwh_sislz;
			parameters[17].Value = model.stwh_slztime;
			parameters[18].Value = model.stwh_sremark;
			parameters[19].Value = model.stwh_saddtime;

			object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(stwh_Model.stwh_staff model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update stwh_staff set ");
			strSql.Append("stwh_dtid=@stwh_dtid,");
			strSql.Append("stwh_snumber=@stwh_snumber,");
			strSql.Append("stwh_sname=@stwh_sname,");
			strSql.Append("stwh_ssex=@stwh_ssex,");
			strSql.Append("stwh_srztime=@stwh_srztime,");
			strSql.Append("stwh_szw=@stwh_szw,");
			strSql.Append("stwh_stel=@stwh_stel,");
			strSql.Append("stwh_sbirthday=@stwh_sbirthday,");
			strSql.Append("stwh_sxueli=@stwh_sxueli,");
			strSql.Append("stwh_ssfz=@stwh_ssfz,");
			strSql.Append("stwh_syqmoney=@stwh_syqmoney,");
			strSql.Append("stwh_szzmoney=@stwh_szzmoney,");
			strSql.Append("stwh_siszz=@stwh_siszz,");
			strSql.Append("stwh_szztime=@stwh_szztime,");
			strSql.Append("stwh_sisht=@stwh_sisht,");
			strSql.Append("stwh_shttime=@stwh_shttime,");
			strSql.Append("stwh_sislz=@stwh_sislz,");
			strSql.Append("stwh_slztime=@stwh_slztime,");
			strSql.Append("stwh_sremark=@stwh_sremark,");
			strSql.Append("stwh_saddtime=@stwh_saddtime");
			strSql.Append(" where stwh_sid=@stwh_sid");
			SqlParameter[] parameters = {
					new SqlParameter("@stwh_dtid", SqlDbType.Int,4),
					new SqlParameter("@stwh_snumber", SqlDbType.Int,4),
					new SqlParameter("@stwh_sname", SqlDbType.NVarChar,200),
					new SqlParameter("@stwh_ssex", SqlDbType.Int,4),
					new SqlParameter("@stwh_srztime", SqlDbType.DateTime),
					new SqlParameter("@stwh_szw", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_stel", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_sbirthday", SqlDbType.DateTime),
					new SqlParameter("@stwh_sxueli", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_ssfz", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_syqmoney", SqlDbType.Int,4),
					new SqlParameter("@stwh_szzmoney", SqlDbType.Int,4),
					new SqlParameter("@stwh_siszz", SqlDbType.Int,4),
					new SqlParameter("@stwh_szztime", SqlDbType.DateTime),
					new SqlParameter("@stwh_sisht", SqlDbType.Int,4),
					new SqlParameter("@stwh_shttime", SqlDbType.DateTime),
					new SqlParameter("@stwh_sislz", SqlDbType.Int,4),
					new SqlParameter("@stwh_slztime", SqlDbType.DateTime),
					new SqlParameter("@stwh_sremark", SqlDbType.NVarChar,500),
					new SqlParameter("@stwh_saddtime", SqlDbType.DateTime),
					new SqlParameter("@stwh_sid", SqlDbType.Int,4)};
			parameters[0].Value = model.stwh_dtid;
			parameters[1].Value = model.stwh_snumber;
			parameters[2].Value = model.stwh_sname;
			parameters[3].Value = model.stwh_ssex;
			parameters[4].Value = model.stwh_srztime;
			parameters[5].Value = model.stwh_szw;
			parameters[6].Value = model.stwh_stel;
			parameters[7].Value = model.stwh_sbirthday;
			parameters[8].Value = model.stwh_sxueli;
			parameters[9].Value = model.stwh_ssfz;
			parameters[10].Value = model.stwh_syqmoney;
			parameters[11].Value = model.stwh_szzmoney;
			parameters[12].Value = model.stwh_siszz;
			parameters[13].Value = model.stwh_szztime;
			parameters[14].Value = model.stwh_sisht;
			parameters[15].Value = model.stwh_shttime;
			parameters[16].Value = model.stwh_sislz;
			parameters[17].Value = model.stwh_slztime;
			parameters[18].Value = model.stwh_sremark;
			parameters[19].Value = model.stwh_saddtime;
			parameters[20].Value = model.stwh_sid;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

        /// <summary>
        /// 修改员工状态
        /// </summary>
        /// <param name="flag">0 转正状态，1签订合同状态，2离职状态</param>
        /// <param name="status">值为0 | 1</param>
        /// <param name="sidlist">员工id</param>
        /// <returns></returns>
        public bool Update(int flag ,int status, string sidlist)
        {
            StringBuilder strSql = new StringBuilder();
            switch (flag)
            {
                case 0:
                    strSql.Append("update stwh_staff set stwh_szztime = getdate(),stwh_siszz = " + status);
                    break;
                case 1:
                    strSql.Append("update stwh_staff set stwh_shttime = getdate(),stwh_sisht = " + status);
                    break;
                default:
                    strSql.Append("update stwh_staff set stwh_slztime = getdate(),stwh_sislz = " + status);
                    break;
            }
            strSql.Append(" where stwh_sid in (" + sidlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(int stwh_sid)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from stwh_staff ");
			strSql.Append(" where stwh_sid=@stwh_sid");
			SqlParameter[] parameters = {
					new SqlParameter("@stwh_sid", SqlDbType.Int,4)
			};
			parameters[0].Value = stwh_sid;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string stwh_sidlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from stwh_staff ");
			strSql.Append(" where stwh_sid in ("+stwh_sidlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public stwh_Model.stwh_staff GetModel(int stwh_sid)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select  top 1 stwh_sid,stwh_dtid,stwh_snumber,stwh_sname,stwh_ssex,stwh_srztime,stwh_szw,stwh_stel,stwh_sbirthday,stwh_sxueli,stwh_ssfz,stwh_syqmoney,stwh_szzmoney,stwh_siszz,stwh_szztime,stwh_sisht,stwh_shttime,stwh_sislz,stwh_slztime,stwh_sremark,stwh_saddtime,stwh_dtname from view_staff ");
			strSql.Append(" where stwh_sid=@stwh_sid");
			SqlParameter[] parameters = {
					new SqlParameter("@stwh_sid", SqlDbType.Int,4)
			};
			parameters[0].Value = stwh_sid;

			stwh_Model.stwh_staff model=new stwh_Model.stwh_staff();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public stwh_Model.stwh_staff DataRowToModel(DataRow row)
		{
			stwh_Model.stwh_staff model=new stwh_Model.stwh_staff();
			if (row != null)
			{
				if(row["stwh_sid"]!=null)
				{
					model.stwh_sid=int.Parse(row["stwh_sid"].ToString());
				}
				if(row["stwh_dtid"]!=null)
				{
					model.stwh_dtid=int.Parse(row["stwh_dtid"].ToString());
				}
				if(row["stwh_snumber"]!=null)
				{
					model.stwh_snumber=int.Parse(row["stwh_snumber"].ToString());
				}
				if(row["stwh_sname"]!=null)
				{
					model.stwh_sname=row["stwh_sname"].ToString();
				}
				if(row["stwh_ssex"]!=null)
				{
					model.stwh_ssex=int.Parse(row["stwh_ssex"].ToString());
				}
                if (row["stwh_srztime"] != null && !string.IsNullOrEmpty(row["stwh_srztime"].ToString()))
				{
					model.stwh_srztime=DateTime.Parse(row["stwh_srztime"].ToString());
				}
				if(row["stwh_szw"]!=null)
				{
					model.stwh_szw=row["stwh_szw"].ToString();
				}
				if(row["stwh_stel"]!=null)
				{
					model.stwh_stel=row["stwh_stel"].ToString();
				}
                if (row["stwh_sbirthday"] != null && !string.IsNullOrEmpty(row["stwh_sbirthday"].ToString()))
				{
					model.stwh_sbirthday=DateTime.Parse(row["stwh_sbirthday"].ToString());
				}
				if(row["stwh_sxueli"]!=null)
				{
					model.stwh_sxueli=row["stwh_sxueli"].ToString();
				}
				if(row["stwh_ssfz"]!=null)
				{
					model.stwh_ssfz=row["stwh_ssfz"].ToString();
				}
				if(row["stwh_syqmoney"]!=null)
				{
					model.stwh_syqmoney=int.Parse(row["stwh_syqmoney"].ToString());
				}
				if(row["stwh_szzmoney"]!=null)
				{
					model.stwh_szzmoney=int.Parse(row["stwh_szzmoney"].ToString());
				}
				if(row["stwh_siszz"]!=null)
				{
					model.stwh_siszz=int.Parse(row["stwh_siszz"].ToString());
				}
                if (row["stwh_szztime"] != null && !string.IsNullOrEmpty(row["stwh_szztime"].ToString()))
				{
					model.stwh_szztime=DateTime.Parse(row["stwh_szztime"].ToString());
				}
				if(row["stwh_sisht"]!=null)
				{
					model.stwh_sisht=int.Parse(row["stwh_sisht"].ToString());
				}
                if (row["stwh_shttime"] != null && !string.IsNullOrEmpty(row["stwh_shttime"].ToString()))
				{
					model.stwh_shttime=DateTime.Parse(row["stwh_shttime"].ToString());
				}
				if(row["stwh_sislz"]!=null)
				{
					model.stwh_sislz=int.Parse(row["stwh_sislz"].ToString());
				}
                if (row["stwh_slztime"] != null && !string.IsNullOrEmpty(row["stwh_slztime"].ToString()))
				{
					model.stwh_slztime=DateTime.Parse(row["stwh_slztime"].ToString());
				}
				if(row["stwh_sremark"]!=null)
				{
					model.stwh_sremark=row["stwh_sremark"].ToString();
				}
                if (row["stwh_saddtime"] != null && !string.IsNullOrEmpty(row["stwh_saddtime"].ToString()))
				{
					model.stwh_saddtime=DateTime.Parse(row["stwh_saddtime"].ToString());
				}
                if (row.ItemArray.Length>21)
                {
                    if (row["stwh_dtname"] != null)
                    {
                        model.stwh_dtname = row["stwh_dtname"].ToString();
                    }
                }
			}
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select stwh_sid,stwh_dtid,stwh_snumber,stwh_sname,stwh_ssex,stwh_srztime,stwh_szw,stwh_stel,stwh_sbirthday,stwh_sxueli,stwh_ssfz,stwh_syqmoney,stwh_szzmoney,stwh_siszz,stwh_szztime,stwh_sisht,stwh_shttime,stwh_sislz,stwh_slztime,stwh_sremark,stwh_saddtime,stwh_dtname ");
            strSql.Append(" FROM view_staff ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
            strSql.Append(" stwh_sid,stwh_dtid,stwh_snumber,stwh_sname,stwh_ssex,stwh_srztime,stwh_szw,stwh_stel,stwh_sbirthday,stwh_sxueli,stwh_ssfz,stwh_syqmoney,stwh_szzmoney,stwh_siszz,stwh_szztime,stwh_sisht,stwh_shttime,stwh_sislz,stwh_slztime,stwh_sremark,stwh_saddtime,stwh_dtname ");
            strSql.Append(" FROM view_staff ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select count(1) FROM view_staff ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.stwh_sid desc");
			}
            strSql.Append(")AS Row, T.*  from view_staff T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString());
		}
		#endregion  BasicMethod
	}
}

